package com.tim.dao.Impl;

import com.tim.dao.PetDao;
import com.tim.pojo.Pet;
import com.tim.util.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * 作者：Tim
 * 时间：2021/4/28 20:23
 */
public class PetDaoImpl implements PetDao {
    @Override
    public int deletePet(int id) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "delete from pet where id = ?";
        Object[] args = {id};
        return qr.update(sql,args);
    }

    @Override
    public int addPet(Pet pet) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "insert into pet (id,image,owner,petName,petColor,adopTime,creatorTime)values(?,?,?,?,?,?,?)";
        Object[] args = {pet.getId(),pet.getImage(),pet.getOwner(),pet.getPetName(),pet.getPetColor(),pet.getAdopTime(),pet.getCreatorTime()};
        return qr.update(sql,args);
    }

    @Override
    public Pet selectOne(int id) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select * from pet where id =?";
        Object[] args = {id};
        return qr.query(sql,new BeanHandler<Pet>(Pet.class),args);
    }

    @Override
    public int updatePet(Pet pet) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "update pet set image=?,owner = ?,petName = ?,petColor=?,adopTime = ?, creatorTime = ?  where id = ?";
        Object[] args={pet.getImage(),pet.getOwner(),pet.getPetName(),pet.getPetColor(),pet.getAdopTime(),pet.getCreatorTime(),pet.getId()};
        return qr.update(sql,args);
    }

    @Override
    public List<Pet> searchPet(String owner) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select * from pet where owner like ?";
        Object[] args = {"%"+owner+"%"};
        return qr.query(sql,new BeanListHandler<Pet>(Pet.class),args);
    }

    @Override
    public List<Pet> limit(Integer currentPage, Integer pageSize, String owner) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        if(owner == null){
            String sql = "select * from pet limit ?,?";
            Object[] args={(currentPage-1)*pageSize,pageSize};
            return qr.query(sql,new BeanListHandler<Pet>(Pet.class),args);
        }else{
            String sql = "select * from pet where plotName like ? limit ?,?";
            Object[] args={"%"+owner+"%",(currentPage-1)*pageSize,pageSize};
            return qr.query(sql,new BeanListHandler<Pet>(Pet.class),args);
        }
    }

    @Override
    public int count(String owner) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        if(owner == null){
            String sql = "select count(*) from pet ";
            //new ScalarHandler<泛型类型>()   单行单列用这个  .intValue()转换为int类型
            return qr.query(sql,new ScalarHandler<Number>()).intValue();
        }else{
            String sql = "select count(*) from pet where owner like ?";
            //new ScalarHandler<泛型类型>()   单行单列用这个  .intValue()转换为int类型
            return qr.query(sql,new ScalarHandler<Number>(),"%"+owner+"%").intValue();
        }
    }
}
